var mysql = require('mysql');

module.exports = {
    getData: function (args) {
        var select = '*';
        if (args.select) {
            args.select.split(',').forEach(function (item) {
                select += ', `' + item.trim() + '`';
            });
            if (select != '*') {
                select = select.substr(2);
            }
        }
        var sql = "select " + select + " from `" + args.TableName + "` where 1=1 ";
        for (var key in args) {
            if (key != "TableName" && key != "orderBy" && key != "pageIndex" && key != "takeNum") {
                if (key.startsWith(">=")) {
                    sql += " and `" + key.substr(2) + "` >= {" + key + "}";
                } else if (key.startsWith("<=")) {
                    sql += " and `" + key.substr(2) + "` <= {" + key + "}";
                } else if (key.startsWith(">")) {
                    sql += " and `" + key.substr(1) + "` > {" + key + "}";
                } else if (key.startsWith("<")) {
                    sql += " and `" + key.substr(1) + "` < {" + key + "}";
                } else if (key.startsWith("*")) {
                    sql += " and locate({" + key + "}, `" + key.substr(1) + "`) > 0";
                } else if (key.startsWith("=")) {
                    sql += " and `" + key.substr(1) + "` = {" + key + "}";
                } else if (key.startsWith("~")) {
                    var keys = args[key].split(',');
                    var k = '';
                    for (var i = 0; i < keys.length; i++) {
                        if (i > 0) {
                            k += ',';
                        }
                        k += mysql.escape(keys[i]);
                    }
                    sql += " and `" + key.substr(1) + "` in (" + k + ")";
                } else {
                    sql += " and `" + key + "` = {" + key + "}";
                }
            }
        }

        if (args.orderBy) {
            var o = args.orderBy.trim().split(/[,| ]/g);
            var orderBy = '';
            for (var i = 0; i < o.length; i++) {
                if (o[i].toLowerCase() != 'asc' && o[i].toLowerCase() != 'desc') {
                    orderBy += ',`' + o[i] + '` ';
                } else if (o[i] != '') {
                    orderBy += o[i];
                }
            }
            sql += ' order by ' + orderBy.substr(1);
        }
        return sql;
    },

    insertData: function (args) {
        var sql = "insert into  ";
        sql += "`" + args.TableName + "`";
        sql += " (";
        for (var key in args) {
            if (key != "TableName")
                sql += "`" + key + "`,";
        }
        sql = sql.substring(0, sql.length - 1);
        sql += " )Value(";
        for (var key in args) {
            if (key != "TableName")
                sql += "{" + key + "},";
        }
        sql = sql.substring(0, sql.length - 1);
        sql += " )";
        return sql;
    },

    delData: function (args) {
        var sql = "delete  from ";
        sql += "`" + args.TableName + "` where 1=1 ";
        for (var key in args) {
            if (key != "TableName") {
                if (key.startsWith(">=")) {
                    sql += " and `" + key.substr(2) + "` >= {" + key + "}";
                } else if (key.startsWith("<=")) {
                    sql += " and `" + key.substr(2) + "` <= {" + key + "}";
                } else if (key.startsWith(">")) {
                    sql += " and `" + key.substr(1) + "` > {" + key + "}";
                } else if (key.startsWith("<")) {
                    sql += " and `" + key.substr(1) + "` < {" + key + "}";
                } else if (key.startsWith("*")) {
                    sql += " and locate({" + key + "}, `" + key.substr(1) + "`) > 0";
                } else if (key.startsWith("=")) {
                    sql += " and `" + key.substr(1) + "` = {" + key + "}";
                } else if (key.startsWith("~")) {
                    var keys = args[key].split(',');
                    var k = '';
                    for (var i = 0; i < keys.length; i++) {
                        if (i > 0) {
                            k += ',';
                        }
                        k += mysql.escape(keys[i]);
                    }
                    sql += " and `" + key.substr(1) + "` in (" + k + ")";
                } else {
                    sql += " and `" + key + "` = {" + key + "}";
                }
            }
        }
        return sql;
    },

    updateData: function (args) {
        var sql = "update  `";
        sql += args.TableName;
        sql += "` SET ";
        for (var key in args) {
            if (key != "TableName" && !key.startsWith('=') && !key.startsWith('*') && !key.startsWith('>') && !key.startsWith('<') && !key.startsWith('~'))
                sql += "`" + key + "`" + " = {" + key + "},";
        }
        sql = sql.substring(0, sql.length - 1);
        var sqlwhere = " where 1=1";
        for (var key in args) {
            if (key.startsWith(">=")) {
                sql += " and `" + key.substr(2) + "` >= {" + key + "}";
            } else if (key.startsWith("<=")) {
                sql += " and `" + key.substr(2) + "` <= {" + key + "}";
            } else if (key.startsWith(">")) {
                sql += " and `" + key.substr(1) + "` > {" + key + "}";
            } else if (key.startsWith("<")) {
                sql += " and `" + key.substr(1) + "` < {" + key + "}";
            } else if (key.startsWith('*')) {
                sqlwhere += " and locate({" + key + "}, `" + key.substr(1) + "`) > 0";
            } else if (key.startsWith('=')) {
                sqlwhere += " and `" + key.substr(1) + "` = {" + key + "}";
            } else if (key.startsWith("~")) {
                var keys = args[key].split(',');
                var k = '';
                for (var i = 0; i < keys.length; i++) {
                    if (i > 0) {
                        k += ',';
                    }
                    k += mysql.escape(keys[i]);
                }
                sqlwhere += " and `" + key.substr(1) + "` in (" + k + ")";
            }
        }
        return sql + sqlwhere;
    },

    tableIsExist: function () {
        return 'select tableName from dw_tableinfo where id={TableId}';
    },

    getSql: function () {
        return 'select * from dw_custom where sqlName={sqlName}';
    },

    getScript: function () {
        return 'select * from dw_customjs where jsName={jsName}';
    }
};